#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
@File  : redis_db.py
@Author: dc_zhao
@Date  : 2021/03/01 下午2:51
"""
import redis
import pymysql
import random
import pandas as pd
import json


class AnalysisWatchStatistic(object):
    """
    艾多美数据统计
    """
    # pymysql 链接配置
    localhost = "rm-uf6daac2q5rg72u0iko.mysql.rds.aliyuncs.com"
    user = "root"
    password = "Youinsh2018"
    db = "ullget"
    charset = "utf8mb4"

    # redis 链接配置
    REDIS_HOST = "r-uf6ff318ee7d78c4.redis.rds.aliyuncs.com"
    REDIS_PORT = "6379"
    REDIS_PASSWORD = "Youinsh2018"
    REDIS_DB = "33"

    def __init__(self):
        pool = redis.ConnectionPool(host=self.REDIS_HOST, port=self.REDIS_PORT, password=self.REDIS_PASSWORD, db=self.REDIS_DB)  # 创建redis连接池
        self.redis = redis.Redis(connection_pool=pool)
        self.db = pymysql.connect(host=self.localhost, user=self.user, password=self.password, db=self.db, charset=self.charset, cursorclass=pymysql.cursors.DictCursor)

    def get_redis_result(self, redis_key):
        """获取redis结果"""
        stream = self.redis.hgetall(redis_key)
        result_list = []
        for i in stream:
            result = self.redis.hget(redis_key, i).decode()
            result_list.append(result)
        return result_list

    def export_out_result(self, redis_key, bit_rate, minutes, start, stop, course_id, enterprise_id=2815):
        """

        :param key:
        :param bit_rate:
        :param minutes:
        :return:
        """
        result = self.get_redis_result(redis_key)
        user_list = list()
        for data in result:
            data = json.loads(data)
            user_id = data.get("user_id")
            user_list.append(user_id)
        print(user_list)

        map = dict()
        user_id_list = []
        form_list = []
        result_data = []
        print("user_list:{}, set_user_list:{}".format(len(user_list), len(set(user_list))))
        for u in range(len(user_list)):
            if user_list[u] in map.keys():
                print(user_list[u])
                user_list[u] = map[user_list[u]]
            else:
                while True:
                    id = random.randrange(start, stop, 1)
                    if id in user_id_list:
                        id = random.randrange(start, stop, 1)
                        continue
                    else:
                        user_id_list.append(id)
                        break
                map[user_list[u]] = id
                user_list[u] = id
        print("user_list:{}, set_user_list:{}".format(len(user_list), len(set(user_list))))
        for user_id, res in zip(user_list, result):
            data = json.loads(res)
            user_id = user_id
            duration = data.get("duration")
            flow = float(duration) * bit_rate * minutes
            flow = flow / 8 / 1024
            flow = str("%.2f" % flow) + 'M'
            end_time = data.get("end_time")
            form_list.append({"user_id": user_id, "flow": flow, "duration": duration, "end_time": end_time})
            result_data.append(
                {
                    "ip": data.get("ip"),
                    "duration": data.get("duration"),
                    "startDate": data.get("start_time"),
                    "endDate": data.get("end_time"),
                    "course_id": course_id,
                    "enterprise_id": enterprise_id,
                    "type": "register_user",
                    "user_id": user_id

                }
            )
        for s in range(0, len(form_list)//20000+1):
            a = form_list[s*20000: 20000*(s+1)]
            df = pd.DataFrame(a, columns=["user_id", "flow", "duration", "end_time"])
            df.columns = ["用户ID", "流量", "观看时长(分钟)", "结束时间"]
            df.to_csv("艾多美数据-{}.csv".format(s), index=False)
        df_result = pd.DataFrame(result_data, columns=["ip", "duration", "startDate", "endDate",
                                                       "course_id", "enterprise_id", "type", "user_id"])
        df_result.to_csv("{}_analysis_watchstatistics.csv".format(course_id), index=False)

    def insert_into_result(self, course_id):
        """插入数据库"""
        db = self.db
        pd_data = pd.read_csv("{}_analysis_watchstatistics.csv".format(course_id), header=0)
        values_list = []
        insert_sql = "insert into analysis_watchstatistics(ip, duration, startDate, endDate, course_id, enterprise_id, type, user_id) values(%s, %s, %s, %s, %s, %s, %s, %s)"
        for index, row in pd_data.iterrows():
            ip = row["ip"]
            duration = row["duration"],
            startDate = row["startDate"],
            endDate = row["endDate"],
            course_id = row["course_id"],
            enterprise_id = row["enterprise_id"],
            type = "register_user",
            user_id = row["user_id"]
            values = (ip, duration[0], startDate[0], endDate[0], course_id[0], enterprise_id[0], type[0], user_id)
            values_list.append(values)
        try:
            with db.cursor() as cursor:
                cursor.executemany(insert_sql, values_list)
                cursor.fetchall()
                db.commit()
                print("插入数据成功")
        except Exception as e:
            print("插入失败:{}".format(e))
            db.rollback()
            print("失败已回滚")
        finally:
            db.close()
            print("链接关闭")

    def get_inquery_mysql(self):
        db = self.db
        try:
            with db.cursor() as cursor:
                sql = "SELECT id, user_id FROM analysis_watchstatistics WHERE course_id=23885"
                cursor.execute(sql)
                result = cursor.fetchall()
                print(result)
        except Exception as e:
            print(e)
        finally:
            db.close()

    def redis_result_list(self, redis_key):
        result = self.get_redis_result(redis_key)
        user_list = list()
        for data in result:
            data = json.loads(data)
            user_id = data.get("user_id")
            user_list.append(user_id)
        print("len_user_list:{}".format(len(user_list), user_list))
        print("len_depu_list:{}".format(len(set(user_list)), set(user_list)))


if __name__ == '__main__':

    aws = AnalysisWatchStatistic()
    key = "24153_static_info"
    aws.get_inquery_mysql()
    aws.redis_result_list(key)
    # # 导出数据
    # redis_result = aws.export_out_result(redis_key=key, bit_rate=1426, minutes=140, start=700000, stop=1000000, course_id=23876)
    # 插入数据库
    # aws.insert_into_result(23876)
